
package edu.cmu.scs.hci.TagIt;

import java.io.File;
import java.io.FilenameFilter;
import java.net.URI;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.os.Environment;
import android.util.Log;

public class PhotosDbAdapter {

	public static final String KEY_ID = "_id";
	//Columns for Photos table
	public static final String KEY_URI = "uri";
	public static final String KEY_TITLE = "title";
	public static final String KEY_DESCRIPTION = "description";
	public static final String TABLE_PHOTOS = "photos";

	//Columns for Tag table
	public static final String TABLE_TAGS = "tags";
	public static final String KEY_TAG_CONTENT = "tagContent";

	//Columns for AppliedTags
	public static final String KEY_PHOTO_ID = "photo_id";
	public static final String KEY_TAG_ID = "tag_id";
	public static final String TABLE_APPLIED_TAGS = "tagApplications";

	private static final String TAG = "PhotosDbAdapter";

	private static final String DATABASE_NAME = "TagIt.db";
	private static final int DATABASE_VERSION = 2;

	private static final String PHOTO_TABLE_CREATE = "CREATE TABLE photos " +
	"(_id integer primary key autoincrement, title text, description text," +
	"uri string not null);";
	private static final String TAG_TABLE_CREATE = "CREATE TABLE tags" +
	"(_id integer primary key autoincrement, tagContent text not null);";
	private static final String TAGAPPLICATIONS_TABLE_CREATE = "CREATE TABLE tagApplications" +
	"(_id integer primay key autoincremnt, photo_id integer, tag_id integer," +
	" FOREIGN KEY (photo_id) REFERENCES photos(_id),  FOREIGN KEY (tag_id) REFERENCES tags(_id));";

	private final Context mCtx;
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(PHOTO_TABLE_CREATE);
			db.execSQL(TAG_TABLE_CREATE);
			db.execSQL(TAGAPPLICATIONS_TABLE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}


	public PhotosDbAdapter(Context ctx) {
		mCtx = ctx;
	}
	
	/**
	 * Makes the databaseAdapter valid. This must be called before using 
	 * any other methods
	 * @return itself
	 * @throws SQLException
	 */
	public PhotosDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		populateDummyDb();
		return this;
	}

	/**
	 * Add a new photo to the database
	 * @param title A title for the photo
	 * @param description A text description
	 * @param uri A uri for where to find the photo
	 * @return The rowId of the photo created by the db
	 */
	public long addPhoto(String title, String description, Uri uri) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_DESCRIPTION, description);
		initialValues.put(KEY_URI, uri.toString());

		return mDb.insert(TABLE_PHOTOS, null, initialValues);
	}

	//Just adds a bunch of photos
	private void populateDummyDb() {
		List<Long> ids = new ArrayList<Long>();
		File rootDir = Environment.getExternalStorageDirectory();
		File [] files = rootDir.listFiles(
		     new FilenameFilter() {
			    public boolean accept(File dir, String filename) {
				     return filename.equals("Pictures");
			    }
		});
		if (files.length == 0)
			return;
		File [] pictures = files[0].listFiles();
		for (int i = 0; i < pictures.length; i++) {
			URI uri_java = pictures[i].toURI();
			Uri uri_android = Uri.parse(uri_java.toString());
			Cursor c = mDb.query(TABLE_PHOTOS, new String [] { KEY_ID }, KEY_URI + "= ?",
					             new String [] {uri_android.toString()}, null, null, null);
			if (c.getCount() == 0) {
				ids.add(addPhoto(pictures[i].getName(), null, uri_android));
			}
			c.close();
		}
		
		for (Long x : ids) {
			addTagToPhoto(x, "puppies");
			addTagToPhoto(x, "kittens");
			addTagToPhoto(x, "bunnies");
		}
	}
	
	/**
	 * Add a tag to a photo with a given rowId. The call will not create dupluciate
	 * instead it will return the rowId of the existing entry in the TagApplication table 
	 * @param photo_id The row id of the photo
	 * @param tagContent The text of the tag
	 * @return The row_id in the tagApplications table
	 */
	public long addTagToPhoto(long photo_id, String tagContent) {
		String [] selArg = {tagContent};
		Cursor tag = mDb.query(TABLE_TAGS, new String [] { KEY_ID  }, KEY_TAG_CONTENT + "= ?", selArg, null, null, null);
		long tag_id;
		if (tag.getCount() == 0) {
			ContentValues initialValue = new ContentValues();
			initialValue.put(KEY_TAG_CONTENT, tagContent);
			tag_id = mDb.insert(TABLE_TAGS, null, initialValue);
		}
		else {
			tag.moveToFirst();
			tag_id = tag.getLong(0);
		}
		
		tag.close();

		return addTagToPhoto(photo_id, tag_id);

	}

	public long addTagToPhoto(long photo_id, long tag_id) {
		Cursor cursor = mDb.query(TABLE_APPLIED_TAGS, new String [] { KEY_ID }, "photo_id = ? AND tag_id = ?", new String [] {photo_id + "", tag_id + ""},
				null, null, null);
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			long result  = cursor.getLong(cursor.getColumnIndex(KEY_ID));
			cursor.close();
			return result;
		}
		else {
			ContentValues initialValue = new ContentValues();
			initialValue.put(KEY_PHOTO_ID, photo_id);
			initialValue.put(KEY_TAG_ID, tag_id);
			return mDb.insert(TABLE_APPLIED_TAGS, null, initialValue);
		}
	}

	/**
	 * Get cursor with all of the tags that are applied to a photo.
	 * @param photo_id The rowId of the photo in question
	 * @return A cursor containing the rowId of the tags and the tagContent for the tags.
	 */
	public Cursor getTags(long photo_id) {
		return mDb.rawQuery("SELECT tags._id, tagContent FROM tags JOIN tagApplications ON tags._id = tagApplications.tag_id WHERE " +
				"photo_id = ?", new String [] {photo_id + ""});
	} 
	
	/**
	 * Get a cursor to photos.
	 * The cursor returned from here is suitable for use with PhotoCursorAdapter.
	 * @param num The number of photos to return in the cursor
	 * @return A cursor containing all of the columns in the photos table.
	 */
	public Cursor selectPhotos(int num) {
		return mDb.query(TABLE_PHOTOS, null, null, null, null, null, null, num + "");
	}
}